package acessoDados.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;

import acessoDados.dao.ReservaDAO;
import constants.ProjectConstants;

public class ReservaDAOImpl extends BaseDAOImpl implements ReservaDAO {
	public  int criaReserva(String login, int idViagem, String tipoPagamento, int quantPagamento) throws SQLException, ClassNotFoundException {
		String query4 = "INSERT INTO reserva (idViagem, login, tipoPagamento, quantPagamento)" + "VALUES (" + idViagem + ", '" + login + "', '" + tipoPagamento + "', " + quantPagamento + ");";
		executaQueryUpdate(query4);
		
		String query5 = "SELECT idReserva FROM reserva WHERE idViagem = " + idViagem + " AND login = '" + login + "' ORDER BY idReserva DESC;";
		ResultSet idreserva = executaQuery(query5);
		
		if(idreserva.next())
			return idreserva.getInt("idReserva");
		return -1;
	}
	
	public  int getNrTotalReservas() throws SQLException, ClassNotFoundException {
		String query = "SELECT COUNT( * ) nrTotal FROM reserva;";
		ResultSet result = executaQuery(query);
		if(result.next())
			return result.getInt("nrTotal");
		else
			return 0;
	}
	public  int getNrTotalReservasCanceladas() throws SQLException, ClassNotFoundException {
		String query = "SELECT COUNT( * ) nrTotal FROM viagem WHERE estadoViagem=" + ProjectConstants.CANCELADO+";";
		ResultSet result = executaQuery(query);
		if(result.next())
			return result.getInt("nrTotal");
		else
			return 0;
	}
	public  void associaLugar(int idReserva, int posicao) throws SQLException, ClassNotFoundException{
		String query = "INSERT INTO lugaraviao (idReserva, posicao)" + "VALUES (" + idReserva + ", " + posicao + ");";
		executaQueryUpdate(query);
	}

	public  void cancelaReserva(String id) throws SQLException, ClassNotFoundException {
		String query = "UPDATE reserva SET estadoReserva=" + ProjectConstants.CANCELADO +
		" WHERE idReserva=" + id + ";";
		executaQueryUpdate(query);
		
		String query2 = "DELETE FROM lugaraviao WHERE idReserva=" + id + ";";
		executaQueryUpdate(query2);
	}

	public  void expiraReserva(String id) throws SQLException, ClassNotFoundException {
		String query = "UPDATE reserva SET estadoReserva=" + ProjectConstants.FECHADO +
		" WHERE idReserva=" + id + ";";

		executaQueryUpdate(query);
	}

	public  ResultSet getReservas(String login) throws SQLException, ClassNotFoundException {
// 		String query = "SELECT idReserva, NomeLocal FROM reserva r, association3 lv, local l;" +
// 				"WHERE r.idViagem=lv.idViagem AND l.idLocal=lv.idLocal AND r.login=" + id;
 		String query = "SELECT r.idReserva, group_concat(l.NomeLocal ORDER BY lv.ordem separator ', ') as titulo" +
					" FROM reserva r, Viagem_Local lv, local l" +
					" WHERE r.idViagem=lv.idViagem AND l.idLocal=lv.idLocal AND r.login='" + login + 
					"' AND r.estadoReserva=" + ProjectConstants.ACTIVO +
					" GROUP BY idReserva;";
 		return executaQuery(query);
	}

	public  ResultSet getReservasDeViagem(String idViagem) throws SQLException, ClassNotFoundException {
 		String query = "SELECT r.*" +
					" FROM reserva r, viagem v" +
					" WHERE r.idViagem=v.idViagem AND v.idViagem=" + idViagem + 
					" AND r.estadoReserva=" + ProjectConstants.ACTIVO +
					";";
 		return executaQuery(query);
	}

	public  ResultSet getDetalhesReserva(String id) throws SQLException, ClassNotFoundException {
		String query = "SELECT r.idReserva, v.diaPartida, v.diaChegada, r.tipoPagamento, r.quantPagamento, a.TipoAviao, vista1.lugares, vista2.locais" +
					" FROM reserva r, aviao a, viagem v," +
					" (" +
					" SELECT r.idReserva, group_concat( CONVERT(la.posicao, CHAR(30)) ORDER BY la.posicao separator ', ') as lugares" +
					" FROM reserva r, lugaraviao la" +
					" WHERE r.idReserva=la.idReserva" +
					" GROUP BY r.idReserva" +
					" ) vista1," +
					" (" +
					" SELECT r.idReserva, group_concat( l.NomeLocal ORDER BY vl.ordem separator ', ') as locais" +
					" FROM reserva r, Viagem_Local vl, local l" +
					" WHERE r.idViagem=vl.idViagem AND vl.idLocal=l.idLocal" +
					" GROUP BY r.idReserva" +
					" )vista2" +
					" WHERE r.idViagem=v.idViagem AND v.idAviao=a.idAviao AND vista1.idReserva=r.idReserva" + 
					" AND vista2.idReserva=r.idReserva AND r.idReserva='" + id + "'" +
					" GROUP BY r.idReserva";
 		return executaQuery(query);
	}

	public int getNumeroReservas(String idViagem) throws SQLException, ClassNotFoundException {
		String query = "SELECT COUNT(*) as reservasFeitas" +
		" FROM lugaraviao la, reserva r" +
		" WHERE la.idReserva=r.idReserva AND r.idViagem=" + idViagem + ";";
		
		ResultSet result = executaQuery(query);
		if(result.next())
			return result.getInt("reservasFeitas");
		
		return 0;
	}

	public  ResultSet getReserva(String id) throws SQLException, ClassNotFoundException {
		String query = "SELECT * FROM reserva WHERE idReserva="+id+";";
		return executaQuery(query);
	}

	public  int getNumeroLugaresDeReserva(int idReserva) throws SQLException, ClassNotFoundException {
		String query = "SELECT COUNT(*) nrLugares FROM lugaraviao WHERE idReserva=" + idReserva + ";";
		ResultSet rs = executaQuery(query);
		
		if(rs.next())
			return rs.getInt("nrLugares");
		return 1;
	}
}
